/*
 * EngineeringDAO.java
 *
 * Created on June 8, 2002, 11:37 PM
 */

package com.scalar.chiptrack.engineering.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.StringTokenizer;
import java.util.Map;
import java.util.List;
import java.io.IOException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.alarms.Alarm;
import com.scalar.chiptrack.alarms.AlarmUser;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.ExcelColumnProperties;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.LotView;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.chiptrack.operations.process.ChiptrackProduct;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.engineering.forms.GraphReportForm;
import com.scalar.chiptrack.engineering.forms.EngineeringReportsForm;
import org.apache.poi.ss.usermodel.Workbook;


/**
 *
 * @author  Mallikarjun
 * @version 1.0
 */

public class EngineeringDAO {

    public static int storeEngPullLot(DBTransaction dbt,
    					LotView lview,
    					String splitLotNumber,
    					int splitQuantity,
    					int pullQuantity) throws ScalarException {
        Connection conn = null;
        CallableStatement cs = null;
        //ResultSet rs = null;
        int engPullID = 0;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if(dbt == null) {
                conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }

            cs = conn.prepareCall(SqlQueryMappings.get("engineering.engpull.insertEngPullLot"));
            cs.setString(1, lview.getLotID());
            cs.setString(2, lview.getProcessStepID());
            cs.setString(3, lview.getLocationID());
            cs.setString(4, lview.getLotNumber());
            cs.setString(5, splitLotNumber);
            cs.setInt(6, splitQuantity);
            cs.setInt(7, pullQuantity);
            cs.registerOutParameter(8, java.sql.Types.INTEGER);

            cs.executeQuery();

            engPullID  = cs.getInt(8);

            success = true;

        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("storeEngPullLot() method failed in EngineeringDAO", sqle);
        }finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }

            if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return engPullID;
    }

    public static int sendAlarm(DBTransaction dbt, String type, User user, LotView lview, String splitLotNumber, int pullQuantity) throws ScalarException{

		Alarm newAlarm = null;
		try {
	         // CREATE ORIGINATOR AND RECIPIENTS
	         ArrayList recipients = new ArrayList();
	         //This should uncomment when the user has e-mail id in database
	         AlarmUser from = new AlarmUser (user.getUserId(), user.getEmail());
	         //AlarmUser from = new AlarmUser ("Mallik", "junk@junk.com");
	         //Loop thru all the users(approvers)
	         //AlarmUser to = new AlarmUser ("User1", "achanala@hotmail.com");//get these from setup
	         //recipients.add (to);
	         //Create the to users
	         AlarmUser to = new AlarmUser("vivek", "vivek_p20002000@yahoo.com");

	         // CREATE EXPIRE DATE
	         Calendar cal = Calendar.getInstance();
	         cal.set (Calendar.MONTH, 6);
	         cal.set (Calendar.DAY_OF_MONTH, 30);
	         Date expiresOn = cal.getTime();

	         recipients.add(to);
               String message = ""; //"Lot Number = "+lview.getLotNumber()+"\n";

	         if(type.equalsIgnoreCase("ENG_PULL")){
	         	newAlarm = new Alarm ("Engineering Pull Lot : "+lview.getLotNumber(), Constants.ENG_PULL_ALARM, from);
			message = mailFormat(lview, splitLotNumber, pullQuantity);

	         }else if(type.equalsIgnoreCase("ENG_SCRAP")){

	         	newAlarm = new Alarm ("Scrap Lot : "+lview.getLotNumber(), Constants.LOT_SCRAP_ALARM , from);
                  message = mailFormat(lview, splitLotNumber, pullQuantity);
	         	//message += "Split LotNumber = "+splitLotNumber+"\n";
	         	//message += "Pull Quantity = "+pullQuantity+"\n";

	         }else if(type.equalsIgnoreCase("ENG_RETURN")){

                        newAlarm = new Alarm ("Eng Return Lot : "+lview.getLotNumber(), Constants.ENG_RETURN_ALARM, from);
				message = mailFormat(lview, splitLotNumber, pullQuantity);

                        /*message = "Quantity = "+lview.getQuantityIn()+"\n";
	         		message += "Split LotNumber = "+splitLotNumber+"\n";
	         		message += "Return Quantity = "+pullQuantity+"\n";
				*/

	         }else if(type.equalsIgnoreCase("LOT_HOLD")){

                 	newAlarm = new Alarm ("Eng Lot Hold : "+lview.getLotNumber(), Constants.LOT_HOLD_ALARM, from);
			message = mailFormat(lview, splitLotNumber, pullQuantity);
                  //message = "Quantity = "+lview.getQuantityIn()+"\n";
	         	//message += " Hold Comments = "+splitLotNumber+"\n";

               }else {//LOT_RELEASE condition

                 	newAlarm = new Alarm ("Eng Lot Release : "+lview.getLotNumber(), Constants.LOT_RELEASE_ALARM,from);
			message = mailFormat(lview, splitLotNumber, pullQuantity);

                  //message = "Quantity = "+lview.getQuantityIn()+"\n";
	         	//message += "Release Comments = "+splitLotNumber+"\n";

                 }

	         newAlarm.setPriority(Alarm.ALARM_PRIORITY_HIGH);
	         newAlarm.setExpiresOn(-1);
	         newAlarm.setRecipients (recipients);
	         newAlarm.setMessage(message);

	         //newAlarm.setMessage ("Please Click here to Approve/Reject this order : http://localhost:8080/chiptrack/approveOrder.do?salesOrderId="+salesOrderId);

	         // ACTIVATE
	         newAlarm.activate(dbt);
	         //System.out.println ("Alarm \"" + newAlarm.getName() + "\" has been activated");
	         //??Balaram why this to.getName() ???only for that particular approver like ???
	         //Comment comment = new Comment (to.getName(), "This sales order should be approved ASAP");
	         //newAlarm.addComment (dbTransaction, comment);

	         //Notify all the reciepents
	         newAlarm.notifyAllRecipients();

       	} catch(Exception ex) {
        		ex.printStackTrace();
            	throw new ScalarException ("Could not create alarm", ex);
       	}

	       	//System.out.println("Alarm ID in sendAlarm() = " + newAlarm.getId());
		return  newAlarm.getId();
	}

	private static String mailFormat(LotView lview, String splitLotNumber, int quantity) {

	        StringBuffer buf = new StringBuffer();

	        buf.append ("<table border='0' width='100%' align=right>\n");
	        buf.append ("<th colspan='4'><h2>Lot Split</h2></th>\n");
	        buf.append ("<tr><td width='20%'><b>Lot Number</b></td>\n");
	        buf.append ("<td width='40%'>" + lview.getLotNumber() + "</td>\n");
	        buf.append ("<td width='20%'><b>Product Name</b></td>\n");
	        buf.append ("<td width='20%'>" + lview.getPartNumber() + "</td>\n");
	        buf.append ("</tr><tr><td width='20%'><b>Current Process</b></td>\n");
	        buf.append ("<td width='40%'>" + lview.getCurrentProcess() + "</td>\n");
	        buf.append ("<td width='20%'><b>Date Code</b></td>\n");
	        buf.append ("<td width='20%'>" + lview.getDateCode() + "</td>\n");
	        buf.append ("</tr><tr><td width='20%'><b>Vendor & Location</b></td>\n");
	        buf.append ("<td width='40%'>" + lview.getVendorLocationName() + "</td>\n");
	        buf.append ("<td width='20%'><b>Status</b></td>\n");
	        buf.append ("<td width='20%'>" + lview.getStatus() + "</td>\n");
	        buf.append ("</tr><tr><td width='20%'><b>Date In</b></td>\n");
	        buf.append ("<td width='40%'>" + lview.getDateIn() + "</td>\n");
	        buf.append ("<td width='20%'><b>Quantity In</b></td>\n");
	        buf.append ("<td width='20%'>" + lview.getQuantityIn() + "</td>\n");
	        buf.append ("</tr><tr><td width='20%'><b>Date Out</b></td>\n");
	        buf.append ("<td width='40%'>" + lview.getDateOut() + "</td>\n");
	        buf.append ("<td width='20%'><b>Quantity Out</b></td>\n");
	        buf.append ("<td width='20%'>" + lview.getQuantityOut() + "</td>\n");
	        buf.append ("</tr><tr><td width='20%'><b>Current Yield</b></td>\n");
	        buf.append ("<td width='40%'>" + lview.getCurrentYield() + "</td>\n");
	        buf.append ("<td width='20%'>&nbsp;</td>\n");
		  buf.append ("<td width='20%'>&nbsp;</td>\n");
	        buf.append ("</tr><tr><td colspan='4'>&nbsp;</td></tr>\n");
		  buf.append ("<tr><td width='20%'><b>Split Lot Number </b></td>\n");
	        buf.append ("<td width='40%'>" + splitLotNumber + "</td>\n");
	        buf.append ("<td width='20%'><b>Split Lot Quantity </b></td>\n");
		  buf.append ("<td width='20%'>" + quantity + "</td>\n");
	        buf.append ("</tr></table>\n");

	        return buf.toString();
    }

	public static void updateAlarmID(DBTransaction dbt, String from, int pullID, int alarmID) throws ScalarException{
		Connection conn = null;
		PreparedStatement ps = null;
		boolean success = false;

		try{
			// Get the connection from regular datasource if its not in a manual transaction
            if (dbt == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbt.getConnection();
            }

            if(from.equalsIgnoreCase("ENG_PULL")){
            	ps = conn.prepareStatement(SqlQueryMappings.get("engineering.engpull.updateEngPullAlarmID"));
            	ps.setInt(1, alarmID);
            	ps.setInt(2, pullID);
            	ps.executeUpdate();

            }

            if(from.equalsIgnoreCase("ENG_SCRAP")){
            	ps = conn.prepareStatement(SqlQueryMappings.get("engineering.engscrap.updateEngScrapAlarmID"));
            	ps.setInt(1, alarmID);
            	ps.setInt(2, pullID);
            	ps.executeUpdate();

            }

            if(from.equalsIgnoreCase("ENG_RETURN")){
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.engreturn.updateEngReturnAlarmID"));
            	ps.setInt(1, alarmID);
            	ps.setInt(2, pullID);
                ps.executeUpdate();

            }

            if(from.equalsIgnoreCase("LOT_HOLD")){
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lotHold.updateLotHoldAlarmID"));
            	ps.setInt(1, alarmID);
            	ps.setInt(2, pullID);
                ps.executeUpdate();

            }

            if(from.equalsIgnoreCase("LOT_RELEASE")){
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lotHold.updateLotReleaseAlarmID"));
            	ps.setInt(1, alarmID);
            	ps.setInt(2, pullID);
                ps.executeUpdate();

            }

            success = true;

        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("updateAlarmID() in EngineeringDAO failed", sqle);
        }finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static int storeEngReturnLot(DBTransaction dbt,
    					 LotView lview,
					 String splitLotNumber,
					 String splitQuantity,
					 String pullQuantity,
					 String flag,
					 String mergeLot,
					 String mergeProcessStepID,
					 String mergeLocationID) throws ScalarException{
        Connection conn = null;
        CallableStatement cs = null;
        //ResultSet rs = null;
        int engPullID = 0;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if(dbt == null) {
                conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }

            cs = conn.prepareCall(SqlQueryMappings.get("engineering.engreturn.insertEngReturnLot"));
            cs.setString(1, lview.getLotID());
            cs.setString(2, lview.getProcessStepID());
            cs.setString(3, lview.getLocationID());
            cs.setString(4, lview.getLotNumber());
            cs.setString(5, splitLotNumber);
            cs.setString(6, splitQuantity);
            cs.setString(7, pullQuantity);
            cs.setString(8, flag);
            cs.setString(9, mergeLot);
            cs.setString(10, mergeProcessStepID);
            cs.setString(11, mergeLocationID);
            cs.registerOutParameter(12, java.sql.Types.INTEGER);

            cs.executeQuery();

            engPullID  = cs.getInt(12);

            success = true;

        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("storeEngReturnLot() method failed in EngineeringDAO", sqle);
        }finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }

            if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return engPullID;
 }

    public static int scrapLot(DBTransaction dbt,
	                			LotView lview,
	                			String splitLotNumber,
	                			int splitQuantity,
	                			int pullQuantity,
	                			int scrapCode) throws ScalarException {

    	Connection conn = null;
        CallableStatement cs = null;
        int scrapID = 0;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if(dbt == null) {
                conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }

            cs = conn.prepareCall(SqlQueryMappings.get("engineering.engscrap.insertEngScrapLot"));
            cs.setString(1, lview.getLotID());
            cs.setString(2, lview.getProcessStepID());
            cs.setString(3, lview.getLocationID());
            cs.setString(4, lview.getLotNumber());
            cs.setString(5, splitLotNumber);
            cs.setInt(6, splitQuantity);
            cs.setInt(7, pullQuantity);
            cs.setInt(8, scrapCode);
            cs.registerOutParameter(9, java.sql.Types.INTEGER);

            cs.executeQuery();

            scrapID  = cs.getInt(9);

            success = true;

        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("scrapLot() method failed in EngineeringDAO", sqle);
        }finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }

            if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return scrapID;
    }


    public static int shipGoodAndScrapReject(DBTransaction dbt,
	                					LotView lview,
	                					String splitLotNumber,
	                					String splitQuantity,
	                					String pullQuantity,
	                					String scrapCode) throws ScalarException {

		Connection conn = null;
        	CallableStatement cs = null;
        	int scrapID = 0;
        	boolean success = false;

        	try {

            	// Get the connection from regular datasource if its not in a manual transaction
            	if(dbt == null) {
                		conn = DataSource.getConnection();
            	}else {
                		conn = dbt.getConnection();
            	}

            	cs = conn.prepareCall(SqlQueryMappings.get("engineering.engscrap.shipGoodAndScrapReject"));
            	cs.setString(1, lview.getLotID());
            	cs.setString(2, lview.getProcessStepID());
            	cs.setString(3, lview.getLocationID());
            	cs.setString(4, lview.getLotNumber());
            	cs.setString(5, splitLotNumber);
            	cs.setString(6, splitQuantity);
            	cs.setString(7, pullQuantity);
            	cs.setString(8, scrapCode);
            	cs.registerOutParameter(9, java.sql.Types.INTEGER);

            	cs.executeQuery();

            	scrapID  = cs.getInt(9);

            	success = true;

        	}catch (Exception sqle) {
            	sqle.printStackTrace();
            	throw new ScalarException ("shipGoodAndScrapReject() method failed in EngineeringDAO", sqle);
        	}finally {

            	// Commit/Rollback here it selef -- if its not in a manual transaction
            	if (dbt == null) {
                		try {
                    		if (conn != null) {
                        		if (success == true) {
                            			conn.commit();
                        		}else {
                            			conn.rollback();
                        		}
                    		}
                		}catch (SQLException ignore) {}
            	}

            	if (cs != null) {
                		try { cs.close(); } catch (SQLException ignore){}
            	}
            	if (conn != null) {
                		try { conn.close(); } catch (SQLException ignore){}
            	}
        	}

        	return scrapID;
	}

    public static String shipGoodAndRetestReject(DBTransaction dbt,
	                					LotView lview,
	                					String splitLotNumber,
	                					int splitQuantity,
	                					int pullQuantity) throws ScalarException {

			Connection conn = null;
        	CallableStatement cs = null;
        	String error_code = "";
        	boolean success = false;

        	try {

            	// Get the connection from regular datasource if its not in a manual transaction
            	if(dbt == null) {
                		conn = DataSource.getConnection();
            	}else {
                		conn = dbt.getConnection();
            	}

            	cs = conn.prepareCall(SqlQueryMappings.get("engineering.retest.shipGoodAndRetestReject"));
            	cs.setString(1, lview.getLotID());
            	cs.setString(2, lview.getProcessStepID());
            	cs.setString(3, lview.getLocationID());
            	cs.setString(4, lview.getLotNumber());
            	cs.setString(5, splitLotNumber);
            	cs.setInt(6, splitQuantity);
            	cs.setInt(7, pullQuantity);
            	cs.registerOutParameter(8, java.sql.Types.VARCHAR);

            	cs.executeQuery();

            	error_code  = cs.getString(9);

            	success = true;

        	}catch (Exception sqle) {
            	sqle.printStackTrace();
            	throw new ScalarException ("shipGoodAndScrapReject() method failed in EngineeringDAO", sqle);
        	}finally {

            	if (dbt == null) {
                		try {
					if (conn != null) {
                        		if (success == true) {
                            			conn.commit();
                        		}else {
                            			conn.rollback();
                        		}
                    		}
                		}catch (SQLException ignore) {}

            		if (cs != null) {
                			try { cs.close(); } catch (SQLException ignore){}
            		}

            		if (conn != null) {
                			try { conn.close(); } catch (SQLException ignore){}
            		}
        		}
		}
        	return error_code;
	}

    public static String lotReleaseRetestWholeLot(DBTransaction dbt,
	                						LotView lview ) throws ScalarException {

		Connection conn = null;
        	CallableStatement cs = null;
        	String error_code = "";
        	boolean success = false;

        	try {

            	// Get the connection from regular datasource if its not in a manual transaction
            	if(dbt == null) {
                		conn = DataSource.getConnection();
            	}else {
                		conn = dbt.getConnection();
            	}

            	cs = conn.prepareCall(SqlQueryMappings.get("engineering.retest.retestWholeLot"));
            	cs.setString(1, lview.getLotID());
            	cs.setString(2, lview.getProcessStepID());
            	cs.setString(3, lview.getLocationID());
            	cs.setString(4, lview.getLotNumber());
            	cs.registerOutParameter(5, java.sql.Types.VARCHAR);

            	cs.executeQuery();

            	error_code  = cs.getString(5);

            	success = true;

        	}catch (Exception sqle) {
            	sqle.printStackTrace();
            	throw new ScalarException ("shipGoodAndScrapReject() method failed in EngineeringDAO", sqle);
        	}finally {

            	// Commit/Rollback here it selef -- if its not in a manual transaction
            	if (dbt == null) {
                		try {
					if (conn != null) {
                        		if (success == true) {
                            			conn.commit();
                        		}else {
                            			conn.rollback();
                        		}
                    		}
                		}catch (SQLException ignore) {}

            		if (cs != null) {
                			try { cs.close(); } catch (SQLException ignore){}
            		}

            		if (conn != null) {
                			try { conn.close(); } catch (SQLException ignore){}
            		}
        		}

        		
		}
        	return error_code;
	}

    public static String lotSendToPreviousLocation(DBTransaction dbt,
	                						LotView lview,
									String previousLotId ) throws ScalarException {

		Connection conn = null;
        	CallableStatement cs = null;
        	String error_code = "";
        	boolean success = false;

        	try {

            	// Get the connection from regular datasource if its not in a manual transaction
            	if(dbt == null) {
                		conn = DataSource.getConnection();
            	}else {
                		conn = dbt.getConnection();
            	}

            	cs = conn.prepareCall(SqlQueryMappings.get("engineering.lot.sendToPreviousStep"));
            	cs.setString(1, lview.getLotID());
            	cs.setString(2, lview.getLotNumber());
			cs.setString(3, lview.getQuantityIn()+"");
			cs.setString(4, previousLotId);
            	cs.registerOutParameter(5, java.sql.Types.VARCHAR);

            	cs.executeQuery();

            	error_code  = cs.getString(5);

            	success = true;

        	}catch (Exception sqle) {
            	sqle.printStackTrace();
            	throw new ScalarException ("lotSendToPreviousLocation() method failed in EngineeringDAO", sqle);
        	}finally {

            	// Commit/Rollback here it selef -- if its not in a manual transaction
            	if (dbt == null) {
                		try {
					if (conn != null) {
                        		if (success == true) {
                            			conn.commit();
                        		}else {
                            			conn.rollback();
                        		}
                    		}
                		}catch (SQLException ignore) {}

            		if (cs != null) {
                			try { cs.close(); } catch (SQLException ignore){}
            		}

            		if (conn != null) {
                			try { conn.close(); } catch (SQLException ignore){}
            		}
        		}

        		
		}
        	return error_code;
	}



    public static ArrayList getPreviousLocationsOfLot(String lotId, String lotNumber) throws ScalarException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = null;

        try{
                conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionPreviousLocations"));
			ps.setString(1, lotId);
			ps.setString(2, lotNumber);
                rs = ps.executeQuery();
                if(rs != null){
                        list = new ArrayList();
                }
                while(rs.next()){
                       list.add(rs.getInt("Lot_ID")+"##"+rs.getString("Location_Name"));
                }
        }catch(Exception e){
                e.printStackTrace();
                throw new ScalarException("getPreviousLocationsOfLot() method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close();}catch(Exception igonore){}
                }
                if(ps != null){
                        try{ps.close();}catch(Exception igonore){}
                }
                if(conn != null){
                        try{conn.close();}catch(Exception igonore){}
                }
        }

        return list;
    }


    public static String mergeLots(DBTransaction dbt,
    				   LotView lview,
	                	   ArrayList mergeLots,
	                	   String mergeQuantity) throws ScalarException {

    	Connection conn = null;
        CallableStatement cs = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String error_code = "";
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if(dbt == null) {
                conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }
            //System.out.println("before Create");
            ps = conn.prepareStatement(SqlQueryMappings.get("engineering.mergeLots.createTemTable"));
            //System.out.println("After Create");
            ps.executeUpdate();

            ps.close();
            ps = null;

            Iterator it = mergeLots.iterator();

            ps = conn.prepareStatement(SqlQueryMappings.get("engineering.mergeLot.tempTableInsert"));

            while(it.hasNext()){
                String s = (String)it.next();
                //System.out.println("StringTokens = "+ s);
                StringTokenizer st = new StringTokenizer(s, "#");

                while(st.hasMoreTokens()){
                        ps.setString(1, st.nextToken());
                        ps.setString(2, st.nextToken());
                        ps.addBatch();
                }
            }

            ps.executeBatch();
            ps.close();
            ps = null;

            /*ps = conn.prepareStatement("select *from #TempTable");

            rs = ps.executeQuery();
            System.out.println("After rs ");
            while(rs.next()){
                System.out.println("Lot Number = "+rs.getString(1)+"; Qty = "+rs.getInt(2));
            }*/

            cs = conn.prepareCall(SqlQueryMappings.get("engineering.lot.lotMerge"));
            cs.setString(1, lview.getLotID());
            cs.setString(2, lview.getProcessStepID());
            cs.setString(3, lview.getLocationID());
            cs.setString(4, lview.getLotNumber());
            cs.setString(5, mergeQuantity);
            //cs.setString(6, error_code);
            cs.registerOutParameter(6, java.sql.Types.VARCHAR);

            cs.executeQuery();

            error_code  = cs.getString(6);

            success = true;

        }catch (Exception sqle) {

            sqle.printStackTrace();
            throw new ScalarException ("mergeLots() method failed in EngineeringDAO", sqle);

        }finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }

            if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return error_code;
    }

    public static int holdLot(DBTransaction dbt, LotView lview, String comments, String flag) throws ScalarException{

        Connection conn = null;
        CallableStatement cs = null;
        //ResultSet rs = null;
        int holdId = 0;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if(dbt == null) {
                conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }

            cs = conn.prepareCall(SqlQueryMappings.get("engineering.lotHold.storedProcHoldLot"));
            cs.setString(1, lview.getLotID());
            cs.setString(2, lview.getProcessStepID());
            cs.setString(3, lview.getLocationID());
            cs.setString(4, lview.getLotNumber());
            cs.setString(5, comments);
            cs.setString(6, flag);

            cs.registerOutParameter(7, java.sql.Types.INTEGER);

            cs.executeQuery();

            holdId  = cs.getInt(7);

            success = true;

        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("holdLot() method failed in EngineeringDAO", sqle);
        }finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }

            if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return holdId;
    }

    public static LotView getCurrentLotPosition(String lotNumber, String status) throws ScalarException{
    	Connection conn = null;
    	CallableStatement cs = null;
    	ResultSet rs = null;
    	int lotId = 0;
    	LotView lotview = null;

    	try{
    		conn = DataSource.getConnection();
    		cs = conn.prepareCall(SqlQueryMappings.get("engineering.lot.storedProcLotPosition"));
    		cs.setString(1, lotNumber);
    		cs.setString(2, status);
    		rs = cs.executeQuery();

    		if(rs.next() != false){
    			lotview = new LotView();
    			lotview.setLotNumber(lotNumber);
    			lotview.setLotID(rs.getInt("Lot_ID")+"");
    			lotview.setPartNumber(rs.getString("Part_Number"));
    			lotview.setCurrentProcess(rs.getString("Current_Process"));
    			if (!"null".equalsIgnoreCase(rs.getString("Date_Code"))){
    				lotview.setDateCode(rs.getString("Date_Code"));
    			}

    			lotview.setLocationID(rs.getInt("Location_ID")+"");
    			lotview.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
    			lotview.setVendorLocationName(rs.getString("Location"));
    			lotview.setStatus(rs.getString("Status"));
    			lotview.setQuantityIn(rs.getInt("Qty_In"));
    			lotview.setQuantityOut(rs.getInt("Qty_out"));
    			lotview.setDateIn(StringUtils.formatDateToString(StringUtils.format(rs.getString("Date_In"), "yyyy-MM-dd"), "MM/dd/yyyy"));
    			lotview.setDateOut(StringUtils.formatDateToString(StringUtils.format(rs.getString("Date_Out"), "yyyy-MM-dd"), "MM/dd/yyyy"));
    			lotview.setCurrentYield(rs.getFloat("Yield"));
    		}

    	}catch(SQLException se){
    		se.printStackTrace();
    		throw new ScalarException(se.getMessage(), se);
    	}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Reading Lot position failed", sqle);
        }finally {
        	if(rs != null){
        		try { rs.close(); } catch (SQLException ignore){}
        	}
            	if (cs != null) {
                	try { cs.close(); } catch (SQLException ignore){}
            	}
            	if (conn != null) {
                	try { conn.close(); } catch (SQLException ignore){}
            	}
        }

        return lotview;
    }

    // Added by veeru on 16th July 2004
    public static LotView getCurrentLotPosition(String lotNumber, String status, String lotId ) throws ScalarException{
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        LotView lotview = null;
        String tempLotId = null;
        try{
            conn = DataSource.getConnection();
            cs = conn.prepareCall(SqlQueryMappings.get("engineering.lot.storedProcLotPosition"));
            cs.setString(1, lotNumber);
            cs.setString(2, status);
            rs = cs.executeQuery();

            while(rs.next()){
                tempLotId = rs.getInt("Lot_ID")+"";
                if( tempLotId.equals(lotId) )
                {
                    lotview = new LotView();
                    lotview.setLotNumber(lotNumber);
                    lotview.setLotID(rs.getInt("Lot_ID")+"");
                    lotview.setPartNumber(rs.getString("Part_Number"));
                    lotview.setCurrentProcess(rs.getString("Current_Process"));
                    if (!"null".equalsIgnoreCase(rs.getString("Date_Code"))){
                        lotview.setDateCode(rs.getString("Date_Code"));
                    }
                    lotview.setLocationID(rs.getInt("Location_ID")+"");
                    lotview.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
                    lotview.setVendorLocationName(rs.getString("Location"));
                    lotview.setStatus(rs.getString("Status"));
                    lotview.setQuantityIn(rs.getInt("Qty_In"));
                    lotview.setQuantityOut(rs.getInt("Qty_out"));
                    lotview.setDateIn(StringUtils.formatDateToString(StringUtils.format(rs.getString("Date_In"), "yyyy-MM-dd"), "MM/dd/yyyy"));
                    lotview.setDateOut(StringUtils.formatDateToString(StringUtils.format(rs.getString("Date_Out"), "yyyy-MM-dd"), "MM/dd/yyyy"));
                    lotview.setCurrentYield(rs.getFloat("Yield"));
                }
            }
            rs.close();
            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.selectProcessType"));
            ps.setInt(1, Integer.parseInt( lotview.getProcessStepID() ) );
            rs = ps.executeQuery();
            while(rs.next())
            {
                lotview.setCurrentProcess(rs.getString("Process_Type"));
            }

        }catch(SQLException se){
            se.printStackTrace();
            throw new ScalarException(se.getMessage(), se);
        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Reading Lot position failed", sqle);
        }finally {
            if(rs != null){
                try { rs.close(); } catch (SQLException ignore){}
            }
                if (cs != null) {
                    try { cs.close(); } catch (SQLException ignore){}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore){}
                }
        }

        return lotview;
    }


    public static LotView getCurrentLotPosition(String lotId) throws ScalarException{
    	Connection conn = null;
    	CallableStatement cs = null;
    	ResultSet rs = null;
    	LotView lotview = null;

    	try{
    		conn = DataSource.getConnection();
    		cs = conn.prepareCall(SqlQueryMappings.get("engineering.lot.storedProcLotPositionByLotId"));
    		cs.setString(1, lotId);
    		rs = cs.executeQuery();

    		if(rs.next() != false){
    			lotview = new LotView();
    			lotview.setLotNumber(rs.getString("Lot_Number"));
    			lotview.setLotID(rs.getInt("Lot_ID")+"");
    			lotview.setPartNumber(rs.getString("Part_Number"));
    			lotview.setCurrentProcess(rs.getString("Current_Process"));
    			lotview.setDateCode(rs.getString("Date_Code"));
    			lotview.setLocationID(rs.getInt("Location_ID")+"");
    			lotview.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
    			lotview.setVendorLocationName(rs.getString("Location"));
    			lotview.setStatus(rs.getString("Status"));
    			lotview.setQuantityIn(rs.getInt("Qty_In"));
    			lotview.setQuantityOut(rs.getInt("Qty_out"));
    			lotview.setDateIn(rs.getString("Date_In"));
    			lotview.setDateOut(rs.getString("Date_Out"));
    			lotview.setCurrentYield(rs.getFloat("Yield"));
    		}

    	}catch(SQLException se){
    		se.printStackTrace();
    		throw new ScalarException(se.getMessage(), se);
    	}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Reading Lot position failed", sqle);
        }finally {
        	if(rs != null){
        		try { rs.close(); } catch (SQLException ignore){}
        	}
            	if (cs != null) {
                	try { cs.close(); } catch (SQLException ignore){}
            	}
            	if (conn != null) {
                	try { conn.close(); } catch (SQLException ignore){}
            	}
        }

        return lotview;
    }


    public static boolean isLotRunning(String lotNumber) throws ScalarException{

    		Connection conn = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    		int lotId = 0;
    		boolean currentStatus = false;

    		try{
    			conn = DataSource.getConnection();
          	ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.selectLotRunning"));
    			ps.setString(1, lotNumber);
    			rs = ps.executeQuery();

    			if(rs.next() != false){
    				lotId = rs.getInt("Lot_ID");
    			}

    			if(lotId != 0){
    				currentStatus = true;
    			}

    		}catch (Exception sqle) {
          	sqle.printStackTrace();
            	throw new ScalarException ("isLotRunning() method failed in EngineeringDAO", sqle);
        	}finally {
        		if(rs != null){
        			try { rs.close(); } catch (SQLException ignore){}
        		}
	        	if (ps != null) {
	              	try { ps.close(); } catch (SQLException ignore){}
	        	}
	        	if (conn != null) {
	                try { conn.close(); } catch (SQLException ignore){}
	        	}
        	}

        	return currentStatus;
    }

    public static ArrayList getLocationsUpToTest(String processStepID) throws ScalarException{
        ArrayList list = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
                conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionLocationUpToTest"));
                ps.setString(1, processStepID);
                rs = ps.executeQuery();

                if(rs != null){
                        list = new ArrayList();
                }

                while(rs.next()){
                        list.add(rs.getString("Location_Name")+"$"+rs.getInt("ProcessStep_ID")+"#"+rs.getInt("Location_ID"));
                }

        }catch(Exception e){
           e.printStackTrace();
           throw new ScalarException("getLocationsUpToTest() method failed in EngineeringDAO", e);

        }finally{
                if(rs != null){
                        try{rs.close(); }catch(Exception ignore){}
                }
                if(ps != null){
                        try{ps.close();}catch(Exception ignore){}
                }
                if(conn != null){
                        try{conn.close(); }catch(Exception ignore){}
                }
        }

        return list;
    }

    public static ArrayList getRunningLots(String processStepID, String locationID) throws ScalarException {
        ArrayList list = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
                conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.runningLots"));
                ps.setString(1, processStepID);
                ps.setString(2, locationID);
                rs = ps.executeQuery();

                if(rs != null){
                        list = new ArrayList();
                }

                while(rs.next()){
                        list.add(rs.getString("Lot_Number"));
                }

        }catch(Exception e){
                e.printStackTrace();
                throw new ScalarException("getLotRunningLots() method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close(); }catch(Exception ignore){}
                }
                if(ps != null){
                        try{ps.close(); }catch(Exception ignore){}
                }
                if(conn != null){
                        try{conn.close(); }catch(Exception ignore){}
                }
        }
        return list;
    }

    public static ArrayList getActiveLots(String productID, String lotNumber) throws ScalarException {
        ArrayList list = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
                conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionActiveLots"));
                ps.setString(1, productID);
                ps.setString(2, lotNumber);
                rs = ps.executeQuery();

                if(rs != null){
                        list = new ArrayList();
                }

    		while(rs.next()){
    			LotView lotview = new LotView();
    			lotview.setLotNumber(rs.getString("Lot_Number"));
    			lotview.setLotID(rs.getInt("Lot_ID")+"");
    			lotview.setCurrentProcess(rs.getString("PS_Type_Name"));
    			lotview.setLocationID(rs.getInt("Location_ID")+"");
    			lotview.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
    			lotview.setStatus(rs.getString("Lot_Current_Status"));
                        list.add(lotview);
    		}

        }catch(Exception e){
                e.printStackTrace();
                throw new ScalarException("getActiveLots() method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close(); }catch(Exception ignore){}
                }
                if(ps != null){
                        try{ps.close(); }catch(Exception ignore){}
                }
                if(conn != null){
                        try{conn.close(); }catch(Exception ignore){}
                }
        }

        return list;
    }
    // Added by veeru on 14th June 2004
    public static ArrayList getActiveLots(String productID, String lotNumber, String processStep) throws ScalarException {
            ArrayList list = null;
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            String processStepId = "";

            try{
                    conn = DataSource.getConnection();
                    if ( "".equals(processStep) || "null".equals(processStep) || processStep == null )
                    {
                        ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionActiveLots"));
                        ps.setString(1, "0");
                        ps.setString(2, lotNumber);
                    }
                    else
                    {
                        ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionActiveLotsWithProcessType"));
                        ps.setString(1, "0");
                        ps.setString(2, lotNumber);
                        ps.setString(3, processStep);
                    }

                    rs = ps.executeQuery();

                    if(rs != null){
                            list = new ArrayList();
                    }

                while(rs.next()){
                    LotView lotview = new LotView();
                    lotview.setLotNumber(rs.getString("Lot_Number"));
                    lotview.setLotID(rs.getInt("Lot_ID")+"");
                    lotview.setCurrentProcess(rs.getString("PS_Type_Name"));
                    lotview.setLocationID(rs.getInt("Location_ID")+"");
                    processStepId = rs.getInt("ProcessStep_ID")+"";
                    lotview.setProcessStepID(processStepId);
                    lotview.setStatus(rs.getString("Lot_Current_Status"));
                    if (  !( "0".equals( productID ) ) && productID.equals( processStepId ) || "0".equals( productID ))
                    {
                        list.add(lotview);
                    }

                }

            }catch(Exception e){
                    e.printStackTrace();
                    throw new ScalarException("getActiveLots() method failed in EngineeringDAO", e);
            }finally{
                    if(rs != null){
                            try{rs.close(); }catch(Exception ignore){}
                    }
                    if(ps != null){
                            try{ps.close(); }catch(Exception ignore){}
                    }
                    if(conn != null){
                            try{conn.close(); }catch(Exception ignore){}
                    }
            }

            return list;
        }



    public static ArrayList getActiveLots(int productID, int processStepID, String lotNumber) throws ScalarException {
        ArrayList list = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
                conn = DataSource.getConnection();

                if( (productID != -1) && (processStepID != -1)){
                        //System.out.println(" pid and psid is not -1");
                	ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionActiveLotsWithProcessStepId"));
                        ps.setString(1, productID+"");
                	ps.setString(2, lotNumber);
                        ps.setString(3, processStepID+"");

                }else if((productID == -1) && (processStepID == -1)){
                	//System.out.println(" pid and psid both -1");
                	ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionActiveLotsWithLotCurrentStatus"));
                        ps.setString(1, "0");
                	ps.setString(2, lotNumber);

                }else{
                        if((productID == -1) && (processStepID != -1)){
                        	//System.out.println(" pid is -1 and psid not -1");
	                        ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionActiveLotsWithProcessStepId"));
	                        ps.setString(1, "0");
	                	ps.setString(2, lotNumber);
	                        ps.setString(3, processStepID+"");
                        }else{
                        	//System.out.println(" pid is not -1 and psid -1");
	                        ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.functionActiveLotsWithLotCurrentStatus"));
	                        ps.setString(1, productID+"");
	                	ps.setString(2, lotNumber);
                        }
                }


                rs = ps.executeQuery();

                if(rs != null){
                        list = new ArrayList();
                }

    		while(rs.next()){
    			LotView lotview = new LotView();
    			lotview.setLotNumber(rs.getString("Lot_Number"));
    			lotview.setLotID(rs.getInt("Lot_ID")+"");
    			lotview.setCurrentProcess(rs.getString("PS_Type_Name"));
    			lotview.setLocationID(rs.getInt("Location_ID")+"");
    			lotview.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
    			lotview.setStatus(rs.getString("Lot_Current_Status"));
                        list.add(lotview);
    		}

        }catch(Exception e){
                e.printStackTrace();
                throw new ScalarException("getActiveLots() override method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close(); }catch(Exception ignore){}
                }
                if(ps != null){
                        try{ps.close(); }catch(Exception ignore){}
                }
                if(conn != null){
                        try{conn.close(); }catch(Exception ignore){}
                }
        }

        return list;
    }


    public static ArrayList getSplitLots(String lotNumber) throws ScalarException
    {
        Connection        conn = null;
        PreparedStatement ps   = null;
        ResultSet         rs   = null;

        boolean flag        = false;
        boolean success     = false;
        boolean tempFlag    = false;
        int     tail        = 0;
        int     splitNumber = 0;

        ArrayList list = null;
        try
        {
            conn = DataSource.getConnection();
            String tempLotNumber = null;
            if(lotNumber.indexOf(".") != -1 )
            {
                 StringTokenizer st = new StringTokenizer(lotNumber, ".");
                 while(st.hasMoreTokens())
                 {
                    tempLotNumber = st.nextToken();
                    try
                    {
                        splitNumber = Integer.parseInt(st.nextToken());
                    }
                    catch( Exception e)
                    {
                        tempFlag = true;
                    }
                 }
                 if( tempFlag )
                 {
                     tempLotNumber = lotNumber;
                 }
            }
            else
            {
                tempLotNumber = lotNumber;
            }
            ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.selectLotSplitNumber"));
            ps.setString(1, tempLotNumber);
            rs = ps.executeQuery();
            if(rs != null){
                 list = new ArrayList();
            }

            if(rs.next() != false){
               tail = rs.getInt("Lot_Split_Number");
            }
            if(tail > 0)
            {
                list.add(lotNumber);
                tail++;
                list.add(tempLotNumber+"."+tail);
                rs.close();
                ps.close();
                flag = true;
            }
            else if(tail == 0)
            {
                tail = splitNumber;
            }
            else
            {
               rs.close();
               rs = null;
               ps.close();
               ps = null;
            }
            if(!flag)
            {
              list = new ArrayList();
              list.add(lotNumber);
              tail++;
              list.add(tempLotNumber+"."+tail);
            }
        }
        catch(Exception e)
        {
                e.printStackTrace();
                throw new ScalarException("getSplitLots() method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close();}catch(Exception ignore){}
                }
                if(ps != null){
                	try{ps.close();}catch(Exception ignore){}
                }
                if(conn != null){
                    try{
                        conn.commit();
                        conn.close();

                    }catch(Exception ignore){
                         success = true;
                    }
                    try{
                        if(success){
                            conn.rollback();
                        }
                    }catch(Exception ignore){}
                }
        }

        return list;
    }
    public static ArrayList getMarketingProducts() throws ScalarException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = null;

        try{
                conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.selectMarketingPartNumber"));
                rs = ps.executeQuery();
                if(rs != null){
                        list = new ArrayList();
                }
                while(rs.next()){
                       list.add(new org.apache.struts.util.LabelValueBean(rs.getString("P_Marketing_Part_Number"), rs.getInt("Product_ID")+""));
                }
        }catch(Exception e){
                e.printStackTrace();
                throw new ScalarException("getMarketingProducts() method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close();}catch(Exception igonore){}
                }
                if(ps != null){
                        try{ps.close();}catch(Exception igonore){}
                }
                if(conn != null){
                        try{conn.close();}catch(Exception igonore){}
                }
        }

        return list;
    }

    public static ArrayList getManufacturingProducts() throws ScalarException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = null;

        try{
                conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.selectManufacturingPartNumber"));
                rs = ps.executeQuery();
                if(rs != null){
                        list = new ArrayList();
                }
                while(rs.next()){
                       list.add(new org.apache.struts.util.LabelValueBean(rs.getString("Part_Number"), rs.getInt("Product_ID")+""));
                }
        }catch(Exception e){
                e.printStackTrace();
                throw new ScalarException("getManufacturingProducts() method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close();}catch(Exception igonore){}
                }
                if(ps != null){
                        try{ps.close();}catch(Exception igonore){}
                }
                if(conn != null){
                        try{conn.close();}catch(Exception igonore){}
                }
        }

        return list;
    }

    public static ArrayList getProcessSteps() throws ScalarException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = null;

        try{
                conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("engineering.lot.selectProcessSteps"));
                rs = ps.executeQuery();
                if(rs != null){
                        list = new ArrayList();
                }
                while(rs.next()){
                       list.add(new org.apache.struts.util.LabelValueBean(rs.getString("PS_Type_Name"), rs.getString("PS_Type_Name")));
                }
        }catch(Exception e){
                e.printStackTrace();
                throw new ScalarException("getManufacturingProducts() method failed in EngineeringDAO", e);
        }finally{
                if(rs != null){
                        try{rs.close();}catch(Exception igonore){}
                }
                if(ps != null){
                        try{ps.close();}catch(Exception igonore){}
                }
                if(conn != null){
                        try{conn.close();}catch(Exception igonore){}
                }
        }

        return list;
    }
    // -- END --
    /**
     * Returns All marketing product names, start with given parameter
     *
     * @author Balaram Putta
     * @param productName to search
     * @throws ScalarException if database operation fails
     */
    public static ArrayList getMarketingProducts ( String productName ) throws ScalarException {

		return getMarketingProducts ( productName, true );
    }

    public static int splitLot(DBTransaction dbt,
    				LotView lview,
	                	String splitLotNumber,
	                	int pullQuantity) throws ScalarException{
    	Connection conn = null;
        CallableStatement cs = null;
        int splitID = 0;
        boolean success = false;
        int splitQuantity = 0;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if(dbt == null) {
                conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }

            splitQuantity = lview.getQuantityIn() - pullQuantity;

            cs = conn.prepareCall(SqlQueryMappings.get ("engineering.lot.lotSplit"));
            cs.setString(1, lview.getLotID());
            cs.setString(2, lview.getProcessStepID());
            cs.setString(3, lview.getLocationID());
            cs.setString(4, lview.getLotNumber());
            cs.setString(5, splitLotNumber);
            cs.setInt(6, splitQuantity);
            cs.setInt(7, pullQuantity);
            cs.registerOutParameter(8, java.sql.Types.INTEGER);

            cs.executeQuery();

            splitID  = cs.getInt(8);

            success = true;

        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("splitLot() method failed in EngineeringDAO", sqle);

        }finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }

            if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return splitID;
    }

    public static void main(String[] args) throws ScalarException{

    }
	public static ArrayList getMarketingProducts ( ArrayList partNumbers ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList list = null;
		String sqlQry = "";
		if ( partNumbers == null ) return null;
		try
		{
			sqlQry += SqlQueryMappings.get( "engineering.lot.searchMarketingPartNumbers" );
			for ( int count = 0; count < partNumbers.size(); count++ )
			{
				sqlQry += " part_number like '";
				sqlQry += partNumbers.get( count );
				sqlQry += "' or ";
			}
			sqlQry = sqlQry.substring ( 0, (sqlQry.length() - 3) );//this is written to remove the last three characters viz "or " to make a proper query.
			sqlQry += ") order by p.P_Marketing_Part_Number";
			conn = DataSource.getConnection();
			ps = conn.prepareStatement( sqlQry );
            rs = ps.executeQuery();
			if ( rs != null)
			{
				list = new ArrayList();
			}
			while ( rs.next() )
			{
				list.add (new ChiptrackProduct (rs.getInt("Product_ID"), rs.getString("P_Marketing_Part_Number")));
			}
		}
		catch (Exception e)
		{
			throw new ScalarException( "Couldnt fetch the marketing part numbers", e );
		}
		finally
		{
			if(rs != null)
			{
				try{ rs.close(); } catch (Exception igonore) {}
			}
			if(ps != null)
			{
				try { ps.close(); } catch (Exception igonore) {}
			}
			if(conn != null)
			{
				try { conn.close(); } catch (Exception igonore) {}
			}
		}
		return list;
	}


    public static ArrayList getMarketingProducts ( String productName, boolean bool ) throws ScalarException
	{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = null;

        if (productName == null) return null;
        try
		{
            conn = DataSource.getConnection();
			String sqlQry = "";
			ps = conn.prepareStatement( SqlQueryMappings.get( "engineering.lot.searchMarketingPartNumber" ) );
			if ( bool )
			{
				ps.setString(1, productName+"%");
			}
			else
			{
				ps.setString( 1, productName );
			}
            rs = ps.executeQuery();

            if(rs != null){
                list = new ArrayList();
            }

            while( rs.next() )
			{
                list.add (new ChiptrackProduct (rs.getInt("Product_ID"), rs.getString("P_Marketing_Part_Number")));
            }
        }
        catch (Exception e)
		{
            throw new ScalarException("Couldn't fetch the marketing part numbers", e);
        }
        finally{
            if(rs != null){
                try{ rs.close(); } catch (Exception igonore) {}
            }
            if(ps != null){
                try { ps.close(); } catch (Exception igonore) {}
            }
            if(conn != null){
                try { conn.close(); } catch (Exception igonore) {}
            }
        }

        return list;
    }
    public static ArrayList getYieldReportDetails( DBTransaction dbTransaction, Map paramMap ) throws ScalarException
    {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList yieldList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }
          String action = (String)paramMap.get("xlsYieldSoftBinReport");
         String counter = (String)paramMap.get( "counter" );
         if ( counter != null && counter.equalsIgnoreCase("SORT"))
         {
            pstmt   = conn.prepareStatement ( "{ CALL sp_sort_history_report( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         }
         else if ( counter != null && counter.equalsIgnoreCase("SORTBIN"))
         {
            pstmt   = conn.prepareStatement ( "{ CALL SP_SORT_BIN_HISTORY_REPORT( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         }
         else if ( counter != null && counter.equalsIgnoreCase("Assembly") )
         {
            pstmt   = conn.prepareStatement ( "{ CALL sp_yield_assembly( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         }
         else
         {
             if ("xlsYieldSoftBinReport".equalsIgnoreCase(action))
             {
                pstmt   = conn.prepareStatement ( "{ CALL SP_TEST_SOFT_BIN_YIELD_REPORT( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
             }
             else
             {
                pstmt   = conn.prepareStatement ( "{ CALL sp_test_bin_yield_report( ?, ?, ?, ?, ?, ?, ?, ? ) }" ); 
             }
         }

         String processStep = (String)paramMap.get( "processStep" );

         pstmt.setString( 1, "SORTBIN".equalsIgnoreCase( processStep ) ? Constants.LOCATION_TYPE_SORT : processStep );
         pstmt.setString( 2, (String)paramMap.get( "psId" ) );
         pstmt.setString( 3, (String)paramMap.get( "baseProductId" ) );
         pstmt.setString( 4, (String)paramMap.get( "vendorName" ) );
         pstmt.setString( 5, (String)paramMap.get( "fromDate" ) );
         pstmt.setString( 6, (String)paramMap.get( "toDate" ) );
         pstmt.setString( 7, (String)paramMap.get( "reportType" ) );
         pstmt.setString( 8, (String)paramMap.get( "defaultPrompt" ) );

         rset    = pstmt.executeQuery();

         yieldList = ExcelUtils.getAllResultSetRows( rset, true );

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Yield records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return yieldList;
    }
    public static String getYield100BinReportDetails( DBTransaction dbTransaction, Map paramMap ) throws ScalarException
    {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;
     String dataString       = null;
     ArrayList yieldList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }
         pstmt   = conn.prepareStatement ( "{ CALL SP_SORT_ALL_BIN_HISTORY_REPORT( ?, ?, ?, ?, ?, ?, ?, ? ) }" );

         String processStep = (String)paramMap.get( "processStep" );

         pstmt.setString( 1, "SORTBIN".equalsIgnoreCase( processStep ) ? Constants.LOCATION_TYPE_SORT : processStep );
         pstmt.setString( 2, (String)paramMap.get( "psId" ) );
         pstmt.setString( 3, (String)paramMap.get( "baseProductId" ) );
         pstmt.setString( 4, (String)paramMap.get( "vendorName" ) );
         pstmt.setString( 5, (String)paramMap.get( "fromDate" ) );
         pstmt.setString( 6, (String)paramMap.get( "toDate" ) );
         pstmt.setString( 7, (String)paramMap.get( "reportType" ) );
         pstmt.setString( 8, (String)paramMap.get( "defaultPrompt" ) );

         rset    = pstmt.executeQuery();

         //yieldList = ExcelUtils.getAllResultSetRows( rset, true );
         dataString = ExcelUtils.getCSVString( rset, true );

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Yield records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return dataString;
    }
    public static Workbook getYieldReportWorkbook ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
    {
        ArrayList yieldList = EngineeringDAO.getYieldReportDetails( dbTransaction, paramMap );
        String counter = (String)paramMap.get( "counter" );
        String fromDate = (String)paramMap.get( "fromDate" );
        String toDate   = (String)paramMap.get( "toDate" );
        String action = (String)paramMap.get("xlsYieldSoftBinReport");
        if ("SORT".equalsIgnoreCase(counter) )
        {
            return DataDumpReportsHelper.getWorkbook( yieldList, "Sort History Report", fromDate, toDate, null );
        }
        else if ("SORTBIN".equalsIgnoreCase(counter) )
        {
            return DataDumpReportsHelper.getWorkbook( yieldList, "Sort History Bin Report",fromDate,toDate,null );
        }
        else if ("Assembly".equalsIgnoreCase(counter) )
        {
             return DataDumpReportsHelper.getWorkbook( yieldList, "Assembly History Report",fromDate,toDate,null );
        }
        else
        {
            if ("xlsYieldSoftBinReport".equalsIgnoreCase(action))
             {
                ExcelColumnProperties colProps1 = new ExcelColumnProperties("Yield");
                colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_FLOAT_PER);

                List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
                list.add(colProps1); 
                return DataDumpReportsHelper.getWorkbook( yieldList, "Test SoftBin History Report",fromDate,toDate,list );
             }
             else
             {
                return DataDumpReportsHelper.getWorkbook( yieldList, "Test History Report",fromDate,toDate,null );
             }
        }
    }
    public static Workbook getYield100BinReportWorkbook ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException,IOException
    {
       // ArrayList yieldList = EngineeringDAO.getYield100BinReportDetails( dbTransaction, paramMap );

        return DataDumpReportsHelper.getWorkbook( null, "Sort History Bin Report",null,null,null );

    }
    public static String getYield100BinReportCSV ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException
    {
        //ArrayList yieldList = EngineeringDAO.getYield100BinReportDetails( dbTransaction, paramMap );
        return EngineeringDAO.getYield100BinReportDetails( dbTransaction, paramMap );
    }

    public static ArrayList getCycleTimeReport( DBTransaction dbTransaction, GraphReportForm form) throws ScalarException
    {
         Connection conn         = null;
         PreparedStatement pstmt = null;
         ResultSet rset          = null;
         boolean success         = false;

         ArrayList yieldList   = new ArrayList();
                                                                                                             
         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }

             pstmt   = conn.prepareStatement ( "{ CALL SP_CYCLE_TIME_REPORT( ?, ?, ?, ?, ?, ?, ? ) }" );

             pstmt.setInt( 1, form.getBaseProductId() );
             pstmt.setString( 2, form.getFromDate() );
             pstmt.setString( 3, form.getToDate() );
             pstmt.setString( 4, form.getProcessType() );
             pstmt.setString( 5, form.getVendorName() );
             pstmt.setInt( 6, form.getProcessStepId() );
             pstmt.setString( 7, "XLSREPORT" );

             rset    = pstmt.executeQuery();

             yieldList = ExcelUtils.getAllResultSetRows( rset, true );

             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load Cycle Time records", e );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }catch ( SQLException e ){}
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return yieldList;
    }

    public static ArrayList getTestBinSummaryYiledReport( DBTransaction dbTransaction, EngineeringReportsForm form) throws ScalarException
    {
         Connection conn         = null;
         PreparedStatement pstmt = null;
         ResultSet rset          = null;
         boolean success         = false;

         ArrayList yieldList   = new ArrayList();

         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }

             pstmt   = conn.prepareStatement ( "{ CALL SP_TEST_SUMMARY_BIN_YIELD_REPORT( ?, ?, ?, ?, ?, ?, ? ) }" );

             pstmt.setString( 1, form.getProcessStep() );
             pstmt.setInt( 2, form.getPsId() );
             pstmt.setInt( 3, form.getBaseProductId() );
             pstmt.setString( 4, form.getVendorName() );  //vendor Id
             pstmt.setString( 5, form.getFromDate() );
             pstmt.setString( 6, form.getToDate() );
             pstmt.setString( 7, form.getReportType() );

             rset    = pstmt.executeQuery();

             yieldList = ExcelUtils.getAllResultSetRows( rset, true );

             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load TETS Summary Yiled Time records", e );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }catch ( SQLException e ){}
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return yieldList;
    }
}


